const express  = require ('express');
const pool = require('../pool');

const router = express.Router();


//搜索全部艺术家
router.get("/queryAllArtist", async (req, res, next) => {
    let pageSize = req.query.pageSize;
    let startIndex = (req.query.nowpage - 1) * pageSize;
    let nowpage=req.query.nowpage
    pool.query(
      `select * from person limit ${startIndex},${pageSize};`,
      async (err, resp) => {
        if (err) {
          return next(err);
        }
        pool.query("select count(*) as count from person", async (err, r) => {
          if (err) {
            return next(err);
          }
          let pageCount = r[0].count;
          if (resp.length != 0) {
            res.send({
              code: 200,
              data: { result: resp, pageCount, nowpage:parseInt(nowpage), pageSize:parseInt(pageSize) },
            });
          } else {
            res.send({ code: 400, data: "没有数据了" });
          }
        });
      }
    );
  });
  //通过姓名模糊查询所有艺术家
  router.get("/queryArtistByName",async (req, res, next) => {
    let pageSize=req.query.pageSize
    let startIndex = (req.query.nowpage - 1) * pageSize;
    let nowpage=req.query.nowpage
    let name=req.query.name
    pool.query(
      `select * from person where p_names like ? limit ${startIndex},${pageSize};`,
      [`%${name}%`],
      async (err, resp) => {
        if (err) {
          return next(err);
        }
        pool.query("select count(*) as count from person where p_names like ?",[`%${name}%`], async (err, r) => {
          if (err) {
            return next(err);
          }
          let pageCount = r[0].count;
          if (resp.length != 0) {
            res.send({
              code: 200,
              data: { result: resp, pageCount, nowpage:parseInt(nowpage), pageSize:parseInt(pageSize) },
            });
          } else {
            res.send({ code: 400, data: "没有数据了" });
          }
        });
      }
    );
  });

router.get('/queryAll',async (req,res,next)=>{
    let page = (req.query.page-1)*18;

    let pageSize = 18; 
    pool.query(`select  p_id,p_names,icon,draw.did,count(draw.did)  from person,draw where p_id=draw.did group by p_names  having(count(draw.did))>=1  limit ${page},${pageSize};`,async(err,resp)=>{
        if(err){
            return next(err)
        };
       console.log(resp,page,)
       pool.query('select count(*) from person',async(err,r)=>{
            if(err){
                return next(err)
            }
            let pageCount = Math.ceil((r[0]["count(*)"]-74)/pageSize)
           if(resp.length !=0){
                res.send({code:200,data:{result:resp,pageCount}})
           }else{
                res.send({code:400,data:'没有数据了'})
           }
       })
    })
})

router.get('/queryById',async (req,res,next)=>{
    pool.query(`select p_names,icon,age,dynasty,deil,count from person where p_id = ${req.query.id}`,[req.query.id],(err,r)=>{
        if(err){
            return next(err)
        }
        let obj =r[0] ;
        let dynasty= r[0].dynasty
        pool.query(`select d_id,d_names,src,look,down,person.p_names,person.dynasty,museum.m_names from draw,person,museum where  did=${req.query.id} and w_id=museum.m_id and did=person.p_id`,async(err,resq)=>{
            if(err){
                return next(err)
            }
            
            pool.query(`select p_id,p_names,icon,age,dynasty from person where dynasty =? && p_id != ${req.query.id}`,[dynasty],async(err,r)=>{
                if(err){
                    return next(err)
                }
                let about = reDate2(r,4)
                res.send({code:200,data:{obj,result:resq,about}})
            })
        })
        
    })
})

router.get('/search',async (req,res,next)=>{
    let name = '%'+req.query.name+'%'
    pool.query('select p_id,p_names,icon,count(draw.did) from person,draw where p_id=draw.did and p_names like ? group by p_names',[name],(err,r)=>{
        if(err){
            return next(err)
        }
        console.log(r)
        if(r.length !==0){
            res.send({code:200,data:r})
        }else{
            res.send({code:400,data:'没有数据了'})
        }
       
    })
})

router.get('/searchPinYin',async(req,res,next)=>{
    let page=(req.query.page-1)*18;
    let p = req.query.name;
    let pageSize = 18
    pool.query(`select p_id,p_names,icon,count(draw.did) from person,draw where p_id=draw.did and pinying=? group by p_names limit ${page},${pageSize}`,[p],(err,r)=>{
        if(err){
            return next(err)
        }
        pool.query('select count(*) from person where pinying= ?',[p],async(err,resp)=>{
            if(err){
                return next(err)
            }
            pageSize = Math.ceil((resp[0]['count(*)']>18?resp[0]['count(*)']-13:resp[0]['count(*)'])/pageSize)
            if(r.length!=0){
                res.send({code:200,data:{result:r,pageSize}})
            }else{
                res.send({code:400,data:'没有数据了'})
            }
        })
        
    })
})

router.get('/searchDynasty',async(req,res,next)=>{
    let page=(req.query.page-1)*18;
    let pageSize = 18;
    let name = req.query.name;
    pool.query(`select p_id,p_names,icon,count(draw.did) from person,draw where p_id=draw.did and dynasty=? group by p_names limit ${page},${pageSize}`,[name],(err,r)=>{
        if(err){
            return next(err)
        }
        pool.query('select count(*) from person where dynasty= ?',[name],(err,resp)=>{
            if(err){
                return next(err)
            }
            let pageCount = Math.ceil(resp[0]['count(*)']/pageSize)
            if(r.length !==0){
                res.send({code:200,data:{result:r,pageCount}})
            }else{
                res.send({code:400,data:'没有数据了'})
            }
        })
    })
})

// 传person表里的所有数据  包括p_id
router.post("/add", (req, res, next) => {
    let name = req.body.p_name; //作者名
    let age = req.body.age; //死亡时间
    let icon = req.body.icon;
    let deil = req.body.deil; //详情 子啥名啥 名号
    let dynasty = req.body.dynasty; //朝代
    let count = req.body.count; //干了啥事
    let pinying = req.body.pinying; //拼音首字母
    pool.query(
      "insert into person values(null,?,?,?,?,?,?,?)",
      [name, age, deil, icon, dynasty, count, pinying],
      (err, r) => {
        if (err) {
          return next(err);
        }
          res.send({ code: 200, msg: "添加成功" });
      }
    );
  });
  
  // router.post("/delete",async (req, res, next) => {
  //   let id=req.query.id
  //   pool.query("delete from person where p_id=?",[id],async (err, r) => {
  //     if (err) {
  //       return next(err);
  //     }
  //     res.send({ code: 200, msg: "删除成功",result:r});
  //   });
  // });
  /**
   * 删除艺术家接口
   * @param:
   *   id:   艺术家id
   * @return:
   *   {code:200, msg:'ok'}
   */
  router.post("/delete", (req, resp,next) => {
    let id=req.body.id
    // 执行删除业务
    let sql = "delete from draw where did=?";
    pool.query(sql, [id], (error, result) => {
      if (error) {
        return next(error);
      }
      pool.query("delete from person where p_id = ?", [id], (error2, result2) => {
        if (error2) {
          return next(error2);
        }
        resp.send({ code: 200, msg: "删除成功" ,result,result2});
      })
    });
  });
  router.post("/update", (req, res, next) => {
    let id = req.body.p_id;
    let name = req.body.p_names; //作者名
    let age = req.body.age; //死亡时间
    let icon = req.body.icon
    let deil = req.body.deil; //详情 子啥名啥 名号
    let dynasty = req.body.dynasty; //朝代
    let count = req.body.count; //干了啥事
    let pinying = req.body.pinying; //拼音首字母
  
    pool.query(
      `update person set p_names=?,age=?,deil=?,icon=?,dynasty=?,count=?,pinying=? where p_id =${id}`,
      [name, age, deil, icon, dynasty, count, pinying],
      (err, r) => {
        if (err) {
          return next(err);
        }
        res.send({ code: 200, msg: "修改成功" ,result:r});
      }
    );
  });
  
  function reDate2(obj, index) {
    if (index === 4) {
      var obj = obj.slice(0, 20);
    }
  
    let newArr = [];
    let mouth = obj.length;
    let num = mouth / index == 0 ? mouth / index : Math.ceil(mouth / index);
    for (let i = 0; i < num; i++) {
      let a = obj.slice(i * index, (i + 1) * index);
      newArr.push(a);
    }
  
    return newArr;
  }
  
  // function reDate(obj){
  //     let arr = [];
  //     let arr2 = [];
  
  //     let num = 1
  //     let str ='';
  //     obj.forEach((v,i) => {
  
  //         if(arr.indexOf(v.p_names)===-1){
  //             arr.push(v.p_names)
  //             str = v.p_names
  
  //             let obj2 = {
  //                 id:v.p_id,
  //                 name:v.p_names,
  //                 num
  //             };
  //             arr2.push(obj2)
  
  //         }else{
  
  //            num++
  //            if(str !== v.p_names){
  //             str = v.p_names
  //             num =1
  //         }
  
  //         }
  
  //     });
  //    return arr2
  // }

module.exports = router;